<?php
/**
 * Created by PhpStorm.
 * User: zhongbaitong
 * Date: 2019/11/29
 * Time: 10:55
 */

namespace sys;

/**
 * 加载 Db 类实现调用此类
 * Class Mysql
 * @package sys
 */
class Mysql extends \mysqli
{
    // 实例对象
    protected static $_instance = null;
    // 表
    protected $tbl = null;
    // 条件
    protected $where = "";
    // 字段，field 被占有
    protected $f = "*";
    // 排序
    protected $order = "";
    // 分页
    protected $limit = "";
    // 关联
    protected $join = "";
    // $lastSql
    protected $lastSql = "";

    /**
     * Db constructor.
     * @param string $host
     * @param string $username
     * @param string $passwd
     * @param string $dbname
     * @param int $port
     * @param string $socket
     */
    public function __construct(string $host, string $username, string $passwd, string $dbname, int $port, string $socket)
    {
        @parent::__construct($host, $username, $passwd, $dbname, $port, $socket);

        if ($this->connect_errno) {
            exit("mysql连接错误[{$this->connect_errno}]：{$this->connect_error}");
        }
        $this->set_charset(config("database.charset"));
    }

    /**
     * 数据库初始化，并取得数据库类实例
     * @access public
     * @param array $options 连接配置
     * @param bool $is_force 连接标识 true 强制重新连接
     * @return Mysql
     */
    public static function getInstance($options = [])
    {
        if (self::$_instance == null) {
            $db_config = config("database");
            self::$_instance = new self(
                $options["host"] ?? $db_config["host"]
                , $options["username"] ?? $db_config["username"]
                , $options["password"] ?? $db_config["password"]
                , $options["database"] ?? $db_config["database"]
                , $options["hostport"] ?? $db_config["hostport"]
                , $options["socket"] ?? $db_config["socket"]
            );
        }
        return self::$_instance;
    }


    /**
     * todo::链式函数封装
     * 模仿 tp db类的风格
     */

    // 无前缀
    public function _table($tbl)
    {
        $this->tbl = $tbl;
        return $this;
    }

    // 拼接表（前缀+$tbl）
    public function _name($tbl)
    {
        $this->tbl = config("database.prefix") . $tbl;
        return $this;
    }

    public function _alias($alias)
    {
        $this->tbl .= " AS {$alias} ";
        return $this;
    }

    /**
     * @param array $where
     * $where = [
     *      ["id",">",1]
     *      ["status","=",1]
     * ]
     * or
     * ["id","=","1"]
     * or
     * [
     *      "id" => 10,
     *      "status" => 1
     * ]
     * or
     * $where = "id > 1"
     * @param string $type OR|AND
     * @return string|null
     */
    public function _where($where, $type = "AND")
    {
        if ($where) {
            if (is_string($where)) {
                $this->where .= " {$type} {$where} ";
            }
            if (is_array($where)) {
                // 一维
                if (count($where) == count($where, 1)) {
                    $keys = array_keys($where);
                    // 索引数组 ["id","=",1"]
                    if($keys === array_keys($keys)){
                        // 符号为 in 时，数组转字符串
                        if(is_string($where[2])){
                            $where[2] = "'{$where[2]}'";
                        }
                        if(strtoupper($where[1]) == "IN"){
                            $where[2] =  "('" . implode("','", $where[2]) . "')";
                        }
                        $this->where .= " $type $where[0] $where[1] $where[2] ";
                    }
                    else{
                        // 组合数组 ["id"=>1]
                        foreach ($where as $key => $val){
                            $this->where .= " $type $key = '$val' ";
                        }
                    }

                } else {
                    // 二维 [["id","=",1"],...]
                    foreach ($where as $k => $v) {
                        // 字符串 加单引号
                        if(is_string($v[2])){
                            $v[2] = "'{$v[2]}'";
                        }
                        // 符号为 in 时，数组转字符串
                        if(strtoupper($v[1]) == "IN"){
                            $v[2] = "('" . implode("','", $v[2]) . "')";
                        }
                        $this->where .= " $type $v[0] $v[1] $v[2] ";
                    }
                }
            }
        }
        return $this;
    }

    // 查询字段
    public function _field($field)
    {
        $this->f = is_array($field)
            ? implode(",", $field)
            : $field;
        return $this;
    }

    // 排序
    public function _order($order)
    {
        if (!$this->order) {
            $this->order = " ORDER BY {$order} ";
        } else {
            $this->order .= " ,$order ";
        }
        return $this;
    }

    // 分页
    public function _page($limit1, $limit2 = "")
    {
        if (!$limit2) {
            $this->limit .= " limit {$limit1} ";
        } else {
            $this->limit .= " limit " . ($limit1 - 1) * $limit2 . "," . $limit2;
        }
        return $this;
    }

    // 表关联
    public function _join($tbl, $condition, $relation = "LEFT")
    {
        $this->join .= " {$relation} JOIN {$tbl}";
        $this->join .= " ON {$condition} ";
        return $this;
    }

    // 获取查询语句的 sql
    public function _getSelectSql()
    {
        $this->lastSql .= "SELECT $this->f FROM $this->tbl ";
        $this->lastSql .= $this->join;
        if ($this->where) {
            $this->where = trim(trim(trim($this->where), "AND"), "OR");
            $this->lastSql .= " WHERE " . $this->where;
        }
        $this->lastSql .= $this->order;
        $this->lastSql .= $this->limit;
        return $this->lastSql;
    }

    // 获取最新的 sql 语句
    public function _getLastSql()
    {
        return $this->lastSql;
    }

    // 单记录查询
    public function _find($just_do_it = true)
    {
        $sql = $this->_getSelectSql();

        $rs = !$just_do_it ? $sql : $this->getFind($sql);
        $this->destroy();
        return $rs;
    }

    // 多记录查询
    public function _select($just_do_it = true)
    {
        $sql = $this->_getSelectSql();

        $rs = !$just_do_it ? $sql : $this->getSelect($sql);
        $this->destroy();
        return $rs;
    }

    // 查询某列
    public function _column($field,$just_do_it = true){
        $sql = $this->_getSelectSql();
        if(!$just_do_it){
            $this->destroy();
            return $sql;
        }
        $rs = $this->getSelect($sql);
        $data = [];
        if(is_string($field)){
            foreach ($rs as $k => $v){
                $data[] = $v[$field];
            }
        }
        else if(is_array($field) && count($field) == 2){
            foreach ($rs as $k => $v){
                $data[$v[$field[0]]] = $v[$field[1]];
            }
        }
        $this->destroy();
        return $data;
    }

    // 删除操作
    public function _delete($just_do_it = true)
    {
        $this->lastSql = "DELETE FROM $this->tbl ";
        $this->where = trim(trim(trim($this->where), "AND"), "OR");
        $this->lastSql .= " WHERE " . $this->where;
        $rs = !$just_do_it ? $this->lastSql : $this->query($this->lastSql);
        $this->destroy();
        return $rs;
    }

    // 修改
    public function _update($data = [], $just_do_it = true)
    {
        $this->lastSql = "UPDATE $this->tbl ";
        $set = "";
        foreach ($data as $field => $val) {
            $set .= "{$field} = '$val',";
        }
        $this->lastSql .= "SET " . trim($set, ",");
        $this->where = trim(trim(trim($this->where), "AND"), "OR");
        $this->lastSql .= " WHERE " . $this->where;
        $rs = !$just_do_it ? $this->lastSql : $this->query($this->lastSql);
        $this->destroy();
        return $rs;
    }

    // 插入
    public function _insert($data = [], $just_do_it = true)
    {
        $this->lastSql = "INSERT INTO $this->tbl ";
        $fields = $values = "";
        foreach ($data as $field => $value) {
            $fields .= "" . $field . ",";
            $values .= "'" . $value . "',";
        }
        $this->lastSql .= "(" . trim($fields, ',') . ") VALUES (" . trim($values, ',') . ")";

        $rs = !$just_do_it ? $this->lastSql
            : ($this->query($this->lastSql) ? $this->insert_id : false);
        $this->destroy();
        return $rs;
    }

    // 插入多条记录
    public function _insertAll($datas = [], $just_do_it = true)
    {
        $this->lastSql = "INSERT INTO $this->tbl ";
        $fields = $values = "";
        foreach ($datas as $k => $data) {
            if ($k === 0) {
                foreach ($data as $field => $value) {
                    $fields .= "{$field},";
                    $values .= "'{$value}',";
                }
                $this->lastSql .= "(" . trim($fields, ',') . ") VALUES (" . trim($values, ',') . ")";
            } else {
                $values = "";
                foreach ($data as $value) {
                    $values .= "'" . $value . "',";
                }
                $this->lastSql .= ",(" . trim($values, ',') . ")";
            }
        }
        $rs = !$just_do_it ? $this->lastSql : $this->query($this->lastSql);
        $this->destroy();
        return $rs;
    }

    // 获取异常
    public function getErr()
    {
        exit("语句错误 [{$this->errno}]：{$this->error}");
    }

    //
    public function getFind($sql)
    {
        $result = $this->query($sql); //mysqli_result对象
        if (!is_object($result)) {
            exit($sql . "语句错误 [{$this->errno}]：{$this->error}");
        }
        $row = $result->fetch_array(MYSQLI_ASSOC);
        return $row;
    }

    //
    public function getSelect($sql)
    {
        //mysqli_result对象
        $result = $this->query($sql);
        if (!is_object($result)) {
            exit($sql . "语句错误 [{$this->errno}]：{$this->error}");
        }
        return $result->fetch_all(MYSQLI_ASSOC);
    }

    // 销毁对象
    // 查询之后，where等成员属性释放
    protected function destroy()
    {
        self::$_instance = null;
    }
}